/*
 * TransLineDAO.java
 *
 * Created on March 5, 2007, 8:08 PM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.util.ArrayList;

/** Reads and writes TransLineBO data to and from the database.
 *
 * @author Travis Kulbeth
 */
public class TransLineDAO {
    
    /** Contains an instance of TransLineDAO */
    private static TransLineDAO instance = null;
    
    /** Creates a new instance of TransLineDAO */
    private TransLineDAO() {
    }
    
    /**Retrieves an instance of the TransLineDAO object
     * @return an instance of TransLineDAO
     */
    public static synchronized TransLineDAO getInstance() {
        if (instance == null) {
            instance = new TransLineDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /** creates a new TransLineBO object.
     *
     *@throws Exception for any errors encountered
     *@return new TransLineBO
     */
    public TransLineBO create() throws Exception{
        String id = GUID.generate();
        TransLineBO bo = new TransLineBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getTxLineID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database by referencing the given id.
     * 
     * @return a TransactionBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized TransLineBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        TransLineBO txLine = (TransLineBO)c.get(id);
        if (txLine != null){
            return txLine;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            txLine = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for Transaction Line ID = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return txLine;
    }
    
    /**
     * This is the public read statement.  It loads an existing TransLine record
     * from the database based on the TransLine id, using an already existing connection to the DB,
     * and returns a TransLineBO.
     * 
     * @return TransLineBO
     * @param id contains the given id for the TransLine to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized TransLineBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        TransLineBO txLine = (TransLineBO)c.get(id);
        if (txLine != null){
            return txLine;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Transaction_Line WHERE TransactionLineID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            txLine = new TransLineBO(rs.getString("TransactionLineID"));
            txLine.setTransactionID(rs.getString("TransactionID"));
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(rs.getString("TypeRS")).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            if(rs.getString("TypeRS") != null){
                txLine.setTypeRS(rs.getString("TypeRS"));
            }
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
            
            // save to the cache
            c.put(txLine.getTxLineID(), txLine);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return txLine;
    }
    
    /**
     * Read all transaction lines for a given transaction
     * 
     * @return list of TransLines for the given Transaction
     * @param transID contains the given id for the Transaction to retrieve the lines for
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<TransLineBO> readByTransaction(String transID) throws DataException, SQLException, ConnectionPoolException{
        ArrayList<TransLineBO> txLineList = new ArrayList<TransLineBO>();
        //now build the query
        String query = "SELECT * FROM Transaction_Line WHERE TransactionID = ?";
        
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, transID);
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            TransLineBO txLine = new TransLineBO(rs.getString("TransactionLineID"));
            txLine.setTransactionID(transID);
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(rs.getString("TypeRS")).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            if(rs.getString("TypeRS") != null){
                txLine.setTypeRS(rs.getString("TypeRS"));
            }
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
            //add to the list
            txLineList.add(txLine);
        }
        
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        return txLineList;
    }
    
    /**
     * Retrieve list of a specific transaction line
     * 
     * @return list of transaction lines for the given transaction containing the given revenueSource
     * @param transID contains the given id for the Transaction to be read
     * @param revSrcID contains the given id for the RevenueSource from the TxLine to be read
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<TransLineBO> retrieveTransLine(String transID, String revSrcID) throws
            DataException, SQLException, ConnectionPoolException{
        ArrayList<TransLineBO> txLineList = new ArrayList<TransLineBO>();
        //now build the query
        String query = "SELECT * FROM Transaction_Line WHERE TransactionID = ? AND RevenueSourceID = ?";
        
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, transID);
        pstmt.setString(2, revSrcID);
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            TransLineBO txLine = new TransLineBO(rs.getString("TransactionLineID"));
            txLine.setTransactionID(rs.getString("TransactionID"));
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(rs.getString("TypeRS")).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            if(rs.getString("TypeRS") != null){
                txLine.setTypeRS(rs.getString("TypeRS"));
            }
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
            //add to the list
            txLineList.add(txLine);
        }
        
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        return txLineList;
    }
    
    /**
     * Read a TransLine by the RevenueSourceID
     * 
     * @return list of TransLines for the given Transaction
     * @param revSrcID contains the RevenueSource ID to read the TransLine by
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized TransLineBO readByRevSrc(String revSrcID) throws DataException, SQLException, ConnectionPoolException{
        TransLineBO txLine = new TransLineBO();
        //now build the query
        String query = "SELECT * FROM Transaction_Line WHERE RevenueSourceID = ?";
        
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, revSrcID);
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            txLine.setTxLineID(rs.getString("TransactionLineID"));
            txLine.setTransactionID(rs.getString("TransactionID"));
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(RS_Handler.getInstance().getRSType(revSrcID)).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            if(rs.getString("TypeRS") != null){
                txLine.setTypeRS(rs.getString("TypeRS"));
            }
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
        }
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        return txLine;
    }
    
    /**
     * Retrieve list of all transaction Lines
     * 
     * @return list of all transaction lines
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<TransLineBO> readAllTxLine() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<TransLineBO> txLineList = new ArrayList<TransLineBO>();
        //now build the query
        String query = "SELECT * FROM Transaction_Line";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            TransLineBO txLine = new TransLineBO(rs.getString("TransactionLineID"));
            txLine.setTransactionID(rs.getString("TransactionID"));
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(rs.getString("TypeRS")).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            if(rs.getString("TypeRS") != null){
                txLine.setTypeRS(rs.getString("TypeRS"));
            }
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
            //add to the list
            txLineList.add(txLine);
        }
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return txLineList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     * 
     * @param txLineIn contains the TransLine object to be saved
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(TransLineBO txLineIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(txLineIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for TransactionLineID = " + txLineIn.getTxLineID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /** This method is called to save a transaction line using a given Connection.
     * 
     * @param txLine contains the TransLineBO to save to the DB
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized void save(TransLineBO txLine, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // call save(bo, conn) on any subobjects (like TransLineDAO to MembershipDAO)
        /*Coupon and RevenueSource objects should already exist in the DB and are not modified by
         *a transaction taking place.  Therefore, we will not save them here.
         */
        
        // run update or insert
        if (txLine.isIsDirty()) {
            if (txLine.isAlreadyInDB()) {
                update(txLine, conn);
            }else{
                insert(txLine, conn);
            }
            // set the dirty flag to false now that we've saved it
            txLine.setIsDirty(false);
        }
        
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(txLine.getTxLineID(), txLine);
    }
    
    /**
     * This method is called by the save operation to update an existing record in the DB.
     * 
     * @param txLine contains the TransLineBO to save to the DB
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void update(TransLineBO txLine, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Transaction_Line SET TransactionID = ?, " +
                "RevenueSourceID = ?, CouponID = ?, LineAmount = ?, DiscountTaken = ?, TypeRS = ? WHERE TransactionLineID = ?");
        pstmt.setString(1, txLine.getTransactionID());
        pstmt.setString(2, txLine.getRevSrc().getRevSrcID());
        //set the coupon id accordingly
        if(txLine.getCoupon() != null){
            pstmt.setString(3, txLine.getCoupon().getCouponID());
        }else{
            pstmt.setNull(3, Types.VARCHAR);
        }
        pstmt.setDouble(4, txLine.getLineAmount());
        pstmt.setDouble(5, txLine.getDiscountTaken());
        pstmt.setString(6, txLine.getTypeRS());
        pstmt.setString(7, txLine.getTxLineID());
        pstmt.executeUpdate();
        pstmt.close();
    }
    
    /**
     * This method is called by the save operation to insert a new record in the DB.
     * 
     * @param txLine contains the TransLineBO to save to the DB
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void insert(TransLineBO txLine, Connection conn) throws SQLException, DataException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Transaction_Line (TransactionLineID, TransactionID, " +
                "RevenueSourceID, CouponID, LineAmount, DiscountTaken, TypeRS) VALUES (?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, txLine.getTxLineID());
        pstmt.setString(2, txLine.getTransactionID());
        pstmt.setString(3, txLine.getRevSrc().getRevSrcID());
        //set the coupon id accordingly
        if(txLine.getCoupon() != null){
            pstmt.setString(4, txLine.getCoupon().getCouponID());
        }else{
            pstmt.setNull(4, Types.VARCHAR);
        }
        pstmt.setDouble(5, txLine.getLineAmount());
        pstmt.setDouble(6, txLine.getDiscountTaken());
        pstmt.setString(7, txLine.getTypeRS());
        pstmt.executeUpdate();
        
        // tell the object that it's now in the db (so we call update next time not insert)
        txLine.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /**
     * I did not include the DELETE functionality intentionally due to instructions received, as well as
     * to protect the integrity of the data.
     * @param RSID contains the RevenueSource id to use to find a specific TransLine
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     * @return a TransLine object for a given RevenueSource ID
     */
    
    
    
    public synchronized TransLineBO getbyRSID(String RSID) throws
            DataException, SQLException, ConnectionPoolException{
        ArrayList<TransLineBO> txLineList = new ArrayList<TransLineBO>();
        //now build the query
        String query = "SELECT * FROM Transaction_Line WHERE RevenueSourceID = ?";
        
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, RSID);
        ResultSet rs = pstmt.executeQuery();
        conn.commit();
        
        TransLineBO txLine = new TransLineBO();
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            txLine = new TransLineBO(rs.getString("TransactionLineID"));
            txLine.setTransactionID(rs.getString("TransactionID"));
            txLine.setRevSrc(RSDAO_Factory.getInstance().getDAO(rs.getString("TypeRS")).read(rs.getString("RevenueSourceID"), conn));
            //set the coupon id if needed
            if(rs.getString("CouponID") != null){
                txLine.setCoupon(CouponDAO.getInstance().read(rs.getString("CouponID"), conn));
            }else{
                CouponBO coup = null;
                txLine.setCoupon(coup);
            }
            txLine.setLineAmount(rs.getDouble("LineAmount"));
            txLine.setDiscountTaken(rs.getDouble("DiscountTaken"));
            txLine.setAlreadyInDB(true);
            txLine.setIsDirty(false);
            
        }
        
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
        return txLine;
    }
    
}
